package src.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import lzit.entity.Shanghus;
import lzit.util.DBUtil;

public class ShanghusDao {
    public  int add(Shanghus m){
        int row=0;
        Connection con=null;
        PreparedStatement stmt=null;
        try{
            con=DBUtil.getCon();
            String sql="insert into SHANGHUS(sh_name,sh_quyu,sh_lianxiren,sh_dianhua)values(?,?,?,?)";
            stmt= con.prepareStatement(sql);
            stmt.setString(1, m.getSh_name());
            stmt.setString(2, m.getSh_quyu());
            stmt.setString(3, m.getSh_lianxiren());
            stmt.setString(4, m.getSh_dianhua());
            row=stmt.executeUpdate();
        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.release(null,stmt,con);
        }
        return row;
    }

    public Shanghus findShById(int id){
        int row=0;
        Connection con=null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try {
            con= DBUtil.getCon();
            String sql="select * from SHANGHUS where id=?";
            stmt= con.prepareStatement(sql);
            stmt.setInt(1,id);
            rs=stmt.executeQuery();
            if(rs.next()){
                Shanghus shanghu=new Shanghus();
                shanghu.setId(rs.getInt("ID"));
                shanghu.setSh_name(rs.getString("SH_NAME"));
                shanghu.setSh_quyu(rs.getString("SH_QUYU"));
                shanghu.setSh_lianxiren(rs.getString("SH_LIANXIREN"));
                shanghu.setSh_dianhua(rs.getString("SH_DIANHUA"));
                return shanghu;
            }

        }catch (SQLException e){
            e.printStackTrace();
        }finally{
            DBUtil.release(rs,stmt,con);;
        }
        return null;

    }

    public  int update(Shanghus m){
        int row=0;
        Connection con=null;
        PreparedStatement stmt=null;
        try{
            con= DBUtil.getCon();
            String sql="update shanghus set sh_name=?,sh_quyu=?,sh_lianxiren=?,sh_dianhua=?where id=?";
            stmt= con.prepareStatement(sql);
            stmt.setString(1, m.getSh_name());
            stmt.setString(2, m.getSh_quyu());
            stmt.setString(3, m.getSh_lianxiren());
            stmt.setString(4, m.getSh_dianhua());
            stmt.setInt(5, m.getId());

            row=stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.release(null,stmt,con);
        }
        return  row;
    }
    public int delete(Shanghus m){
        int row=0;
        Connection con=null;
        PreparedStatement stmt=null;
        try{
            con=DBUtil.getCon();
            String sql="delete from shanghus where id=?";
            stmt=con.prepareStatement(sql);
            stmt.setInt(1,m.getId());

            row=stmt.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.release(null,stmt,con);
        }
        return row;
    }
    public int delete(int id){
        int row=0;
        Connection con=null;
        PreparedStatement stmt=null;
        try {
            con=DBUtil.getCon();
            String sql="delete from shanghus where ID=?";
            stmt= con.prepareStatement(sql);
            stmt.setInt(1,id);

            row=stmt.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.release(null,stmt,con);
        }
        return row;
    }

    public List<Shanghus>findAllSh(String sql){
        Connection con=null;
        Statement stmt=null;
        ResultSet rs=null;

        try {
            con= DBUtil.getCon();
            stmt=con.createStatement();
            rs=stmt.executeQuery(sql);

            List<Shanghus> list=new ArrayList<Shanghus>();
            while (rs.next()){
                Shanghus shanghus=new Shanghus();
                shanghus.setId(rs.getInt("id"));
                shanghus.setSh_name(rs.getString("sh_name"));
                shanghus.setSh_quyu(rs.getString("sh_quyu"));
                shanghus.setSh_lianxiren(rs.getString("sh_lianxiren"));
                shanghus.setSh_dianhua(rs.getString("sh_dianhua"));
                list.add(shanghus);
            }
            return list;
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DBUtil.release(rs,stmt,con);
        }
        return null;
    }
}

